﻿namespace com.vs.DataAccess
{
    using com.vs.General;
    using System;
    using System.Collections;
    using System.Runtime.InteropServices;
    using System.Text;

    public class DBTools
    {
        public const int iMAX_SQL_SERVER_STATEMENTS = 50;
        private const string stERR_NAME = "iError";

        public static void executeMultipleDDLStatements(Session sess, ArrayList sqlVector)
        {
            try
            {
                try
                {
                    for (int i = 0; i < sqlVector.Count; i++)
                    {
                        sqlVector[i] = "dbms_utility.exec_ddl_statement('" + sqlVector[i] + "')";
                    }
                    executeMultipleSQLStatements(sess, sqlVector);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            finally
            {
            }
        }

        public static void executeMultipleSQLStatements(Session session, ArrayList vSQLStatements)
        {
            CDBChange dbChange = null;
            try
            {
                dbChange = new CDBChange(session);
                executeMultipleSQLStatements(session, vSQLStatements, dbChange);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                dbChange = null;
            }
        }

        public static void executeMultipleSQLStatements(Session session, string stSQLStatements)
        {
            CDBChange dbChange = null;
            try
            {
                dbChange = new CDBChange(session);
                executeMultipleSQLStatements(session, stSQLStatements, dbChange);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                dbChange = null;
            }
        }

        public static void executeMultipleSQLStatements(Session session, string[] stSQLStatements)
        {
            CDBChange dbChange = null;
            try
            {
                dbChange = new CDBChange(session);
                executeMultipleSQLStatements(session, stSQLStatements, dbChange);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                dbChange = null;
            }
        }

        public static void executeMultipleSQLStatements(Session session, ArrayList vSQLStatements, CDBChange dbChange)
        {
            try
            {
                if ((vSQLStatements != null) && (vSQLStatements.Count != 0))
                {
                    int iMaxStatements = 50;
                    executeMultipleSQLStatements(session, vSQLStatements, dbChange, iMaxStatements, 0);
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        public static void executeMultipleSQLStatements(Session session, string[] stSQLStatements, CDBChange dbChange)
        {
            int iMaxStatements = 0;
            try
            {
                if ((stSQLStatements != null) && (stSQLStatements.Length != 0))
                {
                    iMaxStatements = 50;
                    executeMultipleSQLStatements(session, stSQLStatements, dbChange, iMaxStatements, 0);
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        public static void executeMultipleSQLStatements(Session session, string stSQLStatements, CDBChange dbChange)
        {
            StringBuilder builder = null;
            try
            {
                if ((stSQLStatements != null) && !stSQLStatements.Equals(""))
                {
                    builder = new StringBuilder();
                    builder.Append("begin ");
                    builder.Append(stSQLStatements);
                    builder.Append(" end;");
                    if (dbChange == null)
                    {
                        dbChange = new CDBChange(session);
                    }
                    int num = 0;
                    num = dbChange.executeSQLChange(builder.ToString());
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                builder = null;
            }
        }

        public static void executeMultipleSQLStatements(Session session, ArrayList vSQLStatements, CDBChange dbChange, int iMaxStatements, int iStatement)
        {
            int num = 0;
            int count = 0;
            StringBuilder builder = null;
            try
            {
                if (((vSQLStatements != null) && (vSQLStatements.Count != 0)) && (iMaxStatements >= 1))
                {
                    count = vSQLStatements.Count;
                    if (iMaxStatements > count)
                    {
                        iMaxStatements = count;
                    }
                    builder = new StringBuilder("begin ");
                    for (int i = iStatement; i < count; i++)
                    {
                        if (num < iMaxStatements)
                        {
                            builder.Append(((string) vSQLStatements[i]).Trim());
                            if (builder[builder.Length - 1] != ';')
                            {
                                builder.Append(";");
                            }
                            num++;
                        }
                        if ((num >= iMaxStatements) || (i == (count - 1)))
                        {
                            try
                            {
                                builder.Append(" end;");
                                if (dbChange == null)
                                {
                                    dbChange = new CDBChange(session);
                                }
                                try
                                {
                                    dbChange.executeSQLChange(builder.ToString());
                                }
                                catch (COMException)
                                {
                                }
                                if (i == (count - 1))
                                {
                                    return;
                                }
                                builder.Length = 0;
                                builder.Append("begin ");
                                num = 0;
                                iStatement = i + 1;
                            }
                            catch (Exception exception2)
                            {
                                if ((exception2.ToString().IndexOf("PLS-00123") < 0) && ((exception2.ToString().IndexOf("ORA-01562") < 0) || (iMaxStatements <= 1)))
                                {
                                    throw exception2;
                                }
                                executeMultipleSQLStatements(session, vSQLStatements, dbChange, iMaxStatements / 2, iStatement);
                                return;
                            }
                        }
                    }
                }
            }
            catch (Exception exception3)
            {
                throw exception3;
            }
            finally
            {
                builder = null;
            }
        }

        public static void executeMultipleSQLStatements(Session session, string[] stSQLArray, CDBChange dbChange, int iMaxStatements, int iStatement)
        {
            int num = 0;
            int length = 0;
            StringBuilder builder = null;
            try
            {
                if (((stSQLArray != null) && (stSQLArray.Length != 0)) && (iMaxStatements >= 1))
                {
                    length = stSQLArray.Length;
                    if (iMaxStatements > length)
                    {
                        iMaxStatements = length;
                    }
                    builder = new StringBuilder("begin ");
                    for (int i = iStatement; i < length; i++)
                    {
                        if (num < iMaxStatements)
                        {
                            builder.Append(stSQLArray[i]).Append("; ");
                            num++;
                        }
                        if ((num >= iMaxStatements) || (i == (length - 1)))
                        {
                            try
                            {
                                builder.Append(" end;");
                                if (dbChange == null)
                                {
                                    dbChange = new CDBChange(session);
                                }
                                try
                                {
                                    dbChange.executeSQLChange(builder.ToString());
                                }
                                catch (COMException)
                                {
                                }
                                if (i == (length - 1))
                                {
                                    return;
                                }
                                builder.Length = 0;
                                builder.Append("begin ");
                                num = 0;
                                iStatement = i + 1;
                            }
                            catch (Exception exception2)
                            {
                                if ((exception2.ToString().IndexOf("PLS-00123") < 0) && ((exception2.ToString().IndexOf("ORA-01562") < 0) || (iMaxStatements <= 1)))
                                {
                                    throw exception2;
                                }
                                executeMultipleSQLStatements(session, stSQLArray, dbChange, iMaxStatements / 2, iStatement);
                                return;
                            }
                        }
                    }
                }
            }
            catch (Exception exception3)
            {
                throw exception3;
            }
            finally
            {
                builder = null;
            }
        }

        public static void executeTransaction(Session sess, ArrayList vecSql)
        {
            CDBChange dbChange = null;
            try
            {
                dbChange = new CDBChange(sess);
                executeTransaction(sess, vecSql, dbChange, null);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                dbChange = null;
                vecSql = null;
            }
        }

        public static void executeTransaction(Session sess, ArrayList vecSql, CDBChange dbChange)
        {
            try
            {
                executeTransaction(sess, vecSql, dbChange, null);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                vecSql = null;
            }
        }

        public static void executeTransaction(Session sess, ArrayList vecSql, ArrayList rowsAffected)
        {
            CDBChange dbChange = null;
            try
            {
                dbChange = new CDBChange(sess);
                executeTransaction(sess, vecSql, dbChange, rowsAffected);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                dbChange = null;
                vecSql = null;
            }
        }

        public static void executeTransaction(Session sess, ArrayList vecSql, CDBChange dbChange, ArrayList rowsAffected)
        {
            int count = 0;
            bool needRowsAffected = false;
            string errMsg = null;
            StringBuilder builder = null;
            string dbType = null;
            try
            {
                if ((vecSql != null) && (vecSql.Count != 0))
                {
                    builder = new StringBuilder();
                    count = vecSql.Count;
                    builder.Append("BEGIN\r\nSET NOCOUNT ON;\r\nBEGIN TRANSACTION;\r\n    declare @iError int;\r\n    declare @stErrorMsg varchar(1000);");
                    for (int i = 0; i < count; i++)
                    {
                        if (rowsAffected != null)
                        {
                            string str3 = (string) rowsAffected[i];
                            if (str3 != null)
                            {
                                needRowsAffected = true;
                                errMsg = str3;
                            }
                        }
                        builder.Append(prepareSQLForTransaction((string) vecSql[i], needRowsAffected, errMsg, dbType));
                        needRowsAffected = false;
                    }
                    builder.Append("\r\nCOMMIT TRANSACTION;\r\nEND;");
                    if (dbChange == null)
                    {
                        dbChange = new CDBChange(sess);
                    }
                    try
                    {
                        dbChange.executeSQLChange(builder.ToString());
                    }
                    catch (COMException)
                    {
                    }
                }
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            finally
            {
                builder = null;
                vecSql = null;
                dbChange = null;
                dbType = null;
            }
        }

        private static string prepareSQLForTransaction(string stSql, bool needRowsAffected, string errMsg, string dbType)
        {
            StringBuilder builder = null;
            string str;
            try
            {
                stSql = stSql.Trim();
                builder = new StringBuilder("\t").Append(stSql).Append(!stSql.EndsWith(";") ? ";\n" : "\n");
                if (StringManager.Equals(dbType, Constants.stSQLSERVER, true))
                {
                    if (stSql.ToUpper().StartsWith("EXEC"))
                    {
                        builder.Insert((stSql.ToUpper().IndexOf("EXEC") + 1) + 5, "@iError=");
                        builder.Append("\n\tif (@").Append("iError").Append(" <> 0").Append("\n\t\tOR @@ERROR <> 0)").Append("\n\tbegin").Append("\n\t\tROLLBACK TRANSACTION;").Append("\n\t\treturn;").Append("\n\tend;").Append("\n").Append("\n");
                    }
                    else if (needRowsAffected)
                    {
                        builder.Append("\n\tif (@@ROWCOUNT = 0 OR @@ERROR <> 0)").Append("\n\tbegin").Append("\n\t\tif (@@ROWCOUNT = 0)").Append("\n\t\tbegin").Append("\n\t\t\tset @stErrorMsg = '").Append(errMsg).Append(": ' + dbo.RSCMfn_RtrvMsg('").Append(errMsg).Append("');").Append("\n\t\t\tRAISERROR(@stErrorMsg, 18, 1);").Append("\n\t\tend;").Append("\n\t\tROLLBACK TRANSACTION;").Append("\n\t\treturn;").Append("\n\tend;").Append("\n").Append("\n");
                    }
                    else
                    {
                        builder.Append("\n\tif (@@ERROR <> 0)").Append("\n\tbegin").Append("\n\t\tROLLBACK TRANSACTION;").Append("\n\t\treturn;").Append("\n\tend;").Append("\n").Append("\n");
                    }
                }
                else if (needRowsAffected)
                {
                    builder.Append("\n\tif (SQL%ROWCOUNT = 0)").Append("\n\tthen").Append("\n\t\tstErrorMsg := '").Append(errMsg).Append(": ' || RSCMfn_RtrvMsg('").Append(errMsg).Append("');").Append("\n\t\tRAISE_APPLICATION_ERROR(-20000, stErrorMsg);").Append("\n\t\tROLLBACK;").Append("\n\t\treturn;").Append("\n\tend if;").Append("\n").Append("\n");
                }
                str = builder.ToString();
            }
            finally
            {
                builder = null;
                stSql = null;
            }
            return str;
        }
    }
}

